Database Tutorials Full-text Index তৈরি করা গাইড ও নোট

Full-text indexing হল একটি বিশেষ ধরনের ইনডেক্স, যা টেক্সট ডেটাতে শব্দের উপর ভিত্তি করে দ্রুত অনুসন্ধান করতে সহায়তা করে। এটি সাধারণত বড় টেক্সট ফিল্ড, যেমন ব্লগ পোস্ট, মন্তব্য বা নথি-এর মধ্যে শব্দের খোঁজ করার জন্য ব্যবহৃত হয়। সাধারণ ইনডেক্স শুধুমাত্র সঠিক ম্যাচিং মান (যেমন, ইকুয়ালিটি বা ইন্ডেক্সের উপর ভিত্তি করে অনুসন্ধান) সনাক্ত করতে সক্ষম, কিন্তু full-text index বিশেষভাবে শব্দ এবং ফ্রেজ অনুসন্ধানের জন্য ডিজাইন করা হয়, যা অনেক দ্রুত এবং দক্ষ।

Full-text index ব্যবহার করে ডেটাবেসে ভিন্ন ভিন্ন শব্দ, ফ্রেজ, বা সম্পূর্ণ বাক্য অনুসন্ধান করা যায়।

Full-text Index এর সুবিধা

1. শব্দ অনুসন্ধান: Full-text index ব্যবহার করে টেক্সট ডেটার মধ্যে শব্দের অনুসন্ধান খুব দ্রুত করা যায়।
2. ফ্রেজ অনুসন্ধান: একটি নির্দিষ্ট শব্দের সংমিশ্রণ বা ফ্রেজের জন্য অনুসন্ধান করা সম্ভব।
3. স্টপওয়ার্ড (Stopwords): সাধারণ ছোট শব্দগুলো (যেমন "এবং", "একটি", "কিন্তু") যেগুলি অনুসন্ধানে ব্যবহার করা হয় না, সেগুলোকে বাদ দেয়া যায়।
4. রিলেভেন্স (Relevance): Full-text index ব্যবহার করে আপনি সহজেই কোন ডকুমেন্ট বা রেকর্ড সবচেয়ে বেশি সম্পর্কিত তা বের করতে পারেন।

Full-text Index তৈরি করা:

1. MySQL এ Full-text Index তৈরি করা

MySQL-এ FULLTEXT ইনডেক্স তৈরি করার জন্য আপনাকে অবশ্যই টেবিলের এমন কলামগুলো নির্বাচন করতে হবে যেগুলোর মধ্যে বড় টেক্সট ডেটা (যেমন TEXT, VARCHAR) থাকবে।

Full-text Index তৈরি করার সিনট্যাক্স:

CREATE FULLTEXT INDEX index_name 
ON table_name (column1, column2, ...);

এটি টেবিলের একটি বা একাধিক কলামে full-text index তৈরি করবে।

উদাহরণ:

ধরা যাক, আপনার articles নামে একটি টেবিল রয়েছে এবং সেখানে title এবং content নামে দুটি কলাম রয়েছে, যেগুলোর মধ্যে টেক্সট ডেটা রয়েছে। যদি আপনি এই কলামগুলিতে Full-text index তৈরি করতে চান, তাহলে আপনি নিম্নলিখিত কমান্ডটি ব্যবহার করতে পারেন:

CREATE FULLTEXT INDEX idx_fulltext
ON articles (title, content);

এটি articles টেবিলের title এবং content কলামে full-text index তৈরি করবে।

2. Full-text Search চালানো:

Full-text index তৈরি করার পর, আপনি MATCH() এবং AGAINST() ফাংশন ব্যবহার করে অনুসন্ধান করতে পারবেন। MATCH() ফাংশন টেক্সটের মধ্যে শব্দের খোঁজ করে এবং AGAINST() ফাংশন দিয়ে আপনি সার্চ টার্ম পাস করবেন।

Full-text Search উদাহরণ:

SELECT * 
FROM articles
WHERE MATCH(title, content) AGAINST('database optimization' IN NATURAL LANGUAGE MODE);

এই কুয়েরি articles টেবিলের title এবং content কলামে 'database optimization' শব্দগুলোর সাথে সম্পর্কিত রেকর্ডগুলো বের করবে।

AGAINST() অপশনসমূহ:

• IN NATURAL LANGUAGE MODE: এটি ডিফল্ট মোড, যেখানে রেলিভেন্ট ডকুমেন্টগুলি প্রদান করা হয়।
• IN BOOLEAN MODE: এখানে আপনাকে বিশেষ কুয়েরি সিম্বল (যেমন +, -, *) ব্যবহার করতে হবে।

উদাহরণ: Boolean Mode ব্যবহার করা

SELECT * 
FROM articles
WHERE MATCH(title, content) AGAINST('+database -optimization' IN BOOLEAN MODE);

এটি database শব্দটি থাকা এবং optimization শব্দটি বাদ দেওয়া ডকুমেন্টগুলিকে খুঁজে বের করবে।

3. MySQL-এ Full-text Index সীমাবদ্ধতা

• FULLTEXT index শুধুমাত্র CHAR, VARCHAR, বা TEXT টাইপের কলামগুলিতে তৈরি করা যেতে পারে।
• Full-text search সব ক্ষেত্রে কার্যকর নয়; এটি টেক্সট-ভিত্তিক ডেটার জন্য বিশেষভাবে উপকারী।
• MySQL-এ Full-text index প্রাক-নির্ধারিত শব্দ বা স্টপওয়ার্ড (যেমন the, is, a ইত্যাদি) বাদ দিয়ে অনুসন্ধান করে। আপনি স্টপওয়ার্ডের তালিকা কাস্টমাইজ করতে পারেন।

PostgreSQL এ Full-text Index তৈরি করা:

PostgreSQL এ GIN (Generalized Inverted Index) অথবা GiST (Generalized Search Tree) ব্যবহার করে Full-text search করতে পারেন।

PostgreSQL Full-text Index তৈরি করার সিনট্যাক্স:

CREATE INDEX index_name 
ON table_name USING gin(to_tsvector('english', column_name));

PostgreSQL উদাহরণ:

ধরা যাক, আপনার documents নামে একটি টেবিল রয়েছে এবং সেখানে content নামের একটি টেক্সট কলাম রয়েছে। Full-text index তৈরি করতে পারেন:

CREATE INDEX idx_fulltext_content
ON documents USING gin(to_tsvector('english', content));

Full-text search ব্যবহার করা:

SELECT * 
FROM documents
WHERE to_tsvector('english', content) @@ plainto_tsquery('english', 'database optimization');

এটি content কলামে 'database optimization' শব্দের সাথে সম্পর্কিত রেকর্ডগুলি বের করবে।

Full-text Index এর উপকারিতা ও সীমাবদ্ধতা

উপকারিতা:

• দ্রুত অনুসন্ধান: টেক্সট ডেটার মধ্যে শব্দ এবং ফ্রেজ অনুসন্ধান করার সময় এটি অনেক দ্রুত।
• ব্যাকগ্রাউন্ড অপারেশন: একটি পূর্ণাঙ্গ টেক্সট ইনডেক্স ডেটাবেসকে দ্রুত পুনরুদ্ধারের সুযোগ দেয়।
• কাস্টম অনুসন্ধান কুয়েরি: Full-text index ব্যবহার করে আপনি কাস্টম টেক্সট অনুসন্ধান করতে পারেন, যেমন স্টপওয়ার্ড বাদ দেওয়া, শব্দের ভিন্ন ভিন্ন ফর্ম ব্যবহার করা ইত্যাদি।

সীমাবদ্ধতা:

• যত বেশি ডেটা, তত বেশি খরচ: বড় ডেটাবেসে ইনডেক্স তৈরি করা ও রক্ষণাবেক্ষণ করা কিছুটা খরচ সাপেক্ষ হতে পারে।
• সঠিক স্টপওয়ার্ড ব্যবস্থাপনা: ইনডেক্সের মধ্যে স্টপওয়ার্ড সঠিকভাবে ব্যবহার না হলে সঠিক ফলাফল আসতে পারে না।

উপসংহার

Full-text index একটি শক্তিশালী টুল যা টেক্সট-ভিত্তিক ডেটা দ্রুত অনুসন্ধানে সহায়তা করে। MySQL এবং PostgreSQL উভয়েই Full-text indexing সমর্থন করে, তবে তাদের নিজস্ব কিছু কনফিগারেশন ও সীমাবদ্ধতা রয়েছে। এটি সঠিকভাবে ব্যবহার করলে আপনার ডেটাবেসের পারফরম্যান্স এবং ব্যবহারকারীর অভিজ্ঞতা অনেক উন্নত হতে পারে।